View¶

Condition for Creating Updatable VIEW¶

A virtual table is updatable if it satisfies the following conditions:

• The FROM clause must include the updatable table or view only.

  In versions lower than CUBRID 9.0, only one updatable table can be included to the FROM clause it requires. However, two tables in parentheses like FROM (class_x, class_y) can be updated since the two were expressed as one table. In version of CUBRID 9.0 or higher, more than one updatable table is allowed. The FROM clause must include only one table or updatable view. However, two tables included in parentheses as in FROM (class_x, class_y) can be updated because they represent one table.

• A JOIN syntax cannot be included.

• The DISTINCT or UNIQUE statement should not be included.

• The GROUP BY... HAVING statement should not be included.

• Aggregate functions such as SUM or AVG should not be included.

• The entire query must consist of queries that can be updated by UNION ALL, not by UNION. However, the table should exist only in one of the queries that constitute UNION ALL.

• If a record is inserted into a view created by using the UNION ALL statement, the system determines into which table the record will be inserted. This cannot be done by the user. To control this, the user must manually insert the row or create a separate view for insertion.

Even when all rules above are satisfied, columns that contains following contents cannot be updated.

• Path expressions (example: tbl_name.col_name)
• Numeric type column that includes an arithmetic operator

Even though the column defined in the view is updatable, a view can be updated only when an appropriate update authorization is granted on the table included in the FROM clause. Also there must be an access authorization to a view. The way to grant an access authorization to a view is the same to grant an access authorization to a table. For details on granting authorization, see Granting Authorization.

ADD QUERY Clause¶

You can add a new query to a query specification by using the ADD QUERY clause of the ALTER VIEW statement. 1 is assigned to the query defined when a virtual table was created, and 2 is assigned to the query added by the ADD QUERY clause.

ALTER [VIEW | VCLASS] view_name
ADD QUERY <select_statement>
[INHERIT <resolution> , ...] ;

    <resolution> ::= {column_name} OF superclass_name [AS alias]

• view_name: Specifies the name of a view where the query to be added.
• <select_statement>: Specifies the query to be added.

SELECT * FROM b_view;

           id  phone
===================================
            1  '111-1111'
            2  '222-2222'
            3  '333-3333'
            4  NULL
            5  NULL

ALTER VIEW b_view ADD QUERY SELECT * FROM a_tbl WHERE id IN (1,2);
SELECT * FROM b_view;

           id  phone
===================================
            1  '111-1111'
            2  '222-2222'
            3  '333-3333'
            4  NULL
            5  NULL
            1  '111-1111'
            2  '222-2222'

AS SELECT Clause¶

You can change the SELECT query defined in the virtual table by using the AS SELECT clause in the ALTER VIEW statement. This function is working like the CREATE OR REPLACE statement. You can also change the query by specifying the query number 1 in the CHANGE QUERY clause of the ALTER VIEW statement.

ALTER [VIEW | VCLASS] view_name AS <select_statement> ;

• view_name: Specifies the name of a view to be modified.
• <select_statement>: Specifies the new query statement to replace the SELECT statement defined when a view is created.

ALTER VIEW b_view AS SELECT * FROM a_tbl WHERE phone IS NOT NULL;
SELECT * FROM b_view;

           id  phone
===================================
            1  '111-1111'
            2  '222-2222'
            3  '333-3333'

CHANGE QUERY Clause¶

You can change the query defined in the query specification by using the CHANGE QUERY clause reserved word of the ALTER VIEW statement.

ALTER [VIEW | VCLASS] view_name
CHANGE QUERY [integer] <select_statement> ;

• view_name: Specifies the name of a view to be modified.
• integer: Specifies the number value of the query to be modified. The default value is 1.
• <select_statement>: Specifies the new query statement to replace the query whose query number is integer.

--adding select_statement which query number is 2 and 3 for each
ALTER VIEW b_view ADD QUERY SELECT * FROM a_tbl WHERE id IN (1,2);
ALTER VIEW b_view ADD QUERY SELECT * FROM a_tbl WHERE id = 3;
SELECT * FROM b_view;

           id  phone
===================================
            1  '111-1111'
            2  '222-2222'
            3  '333-3333'
            4  NULL
            5  NULL
            1  '111-1111'
            2  '222-2222'
            3  '333-3333'

--altering view changing query number 2
ALTER VIEW b_view CHANGE QUERY 2 SELECT * FROM a_tbl WHERE phone IS NULL;
SELECT * FROM b_view;

           id  phone
===================================
            1  '111-1111'
            2  '222-2222'
            3  '333-3333'
            4  NULL
            5  NULL
            4  NULL
            5  NULL
            3  '333-3333'

DROP QUERY Clause¶

You can drop a query defined in the query specification by using the DROP QUERY of the ALTER VIEW statement.

ALTER VIEW b_view DROP QUERY 2,3;
SELECT * FROM b_view;

           id  phone
===================================
            1  '111-1111'
            2  '222-2222'
            3  '333-3333'
            4  NULL
            5  NULL